<?php

/**
 * This provides a user-interface for using the DbDiff class.
 *   
 * More information on this tool can be found at:
 * http://joefreeman.co.uk/blog/2009/07/php-script-to-compare-mysql-database-schemas/
 * 
 * Copyright (C) 2009, Joe Freeman <joe.freeman@bitroot.com>
 * Available under http://en.wikipedia.org/wiki/MIT_License
 */

error_reporting(E_ALL);

require_once('dbdiff.php');
require('config.php');


/**
 * Display options and instructions.
 *
 * @return void
 */
function show_options($dbs_config) {
  
  echo '<h3>Step 1: Export database schemas</h3>';
  
  if (count($dbs_config) > 0) {
    
    echo '<p class="info">Select a database configuration from the list below, or select \'Enter details...\'</p>';
    
    echo '<ul id="db-list">';
    foreach ($dbs_config as $key => $db_config) {
      echo '<li><a href="?a=export_schema&db=' . $key . '">' . $db_config['name'] . '</a></li>';
    }
    echo '<li><em><a href="#" onclick="document.getElementById(\'db-config\').style.display=\'block\';return false;">Enter details...</a></em></li>';
    echo '</ul>';
    
  } else {
    
    echo '<p class="info">Enter connection details in the form below, or setup a database connection in the <code>config.php</code> file.</p>';
  }
  
  echo '<form action="?a=export_schema" method="post" id="db-config"' . (count($dbs_config) > 0 ? ' style="display:none;"' : '' ) . '>';
  echo '<div class="field"><label for="db-host">Host</label><input type="text" name="db-host" id="db-host" value="localhost" /></div>';
  echo '<div class="field"><label for="db-user">User</label><input type="text" name="db-user" id="db-user" /></div>';
  echo '<div class="field"><label for="db-password">Password</label><input type="password" name="db-password" id="db-password" /></div>';
  echo '<div class="field"><label for="db-name">Database</label><input type="text" name="db-name" id="db-name" /></div>';
  echo '<div class="submit"><input type="submit" value="Export" /></div><div class="clearer"></div>';
  echo '</form>';
  
  echo '<h3>Step 2: Compare schemas</h3>';
  
  echo '<p class="info">Once two database schemas have been exported, paste them here to be compared.</p>';
  
  echo '<form action="?a=compare" method="post" id="compare">';
  echo '<div class="field" id="div_schema_1" ><label for="schema1">First schema</label><input id="btn_get_from_rep1" type="button" value="Get latest Snapshot from repository"  />';
  echo '<input id="txt_snapshot_name1" /><textarea name="schema1" id="schema1" cols="100" rows="5"></textarea></div>';
  echo '<div class="field" id="div_schema_2" ><label for="schema2">Second schema</label><input id="btn_get_from_rep2" type="button" value="Get latest Snapshot from repository"  />';
  echo '<input id="txt_snapshot_name2" /><textarea name="schema2" id="schema2" cols="100" rows="5"></textarea></div>';
  echo '<div class="submit"><input type="submit" value="Compare" /></div>';
  echo '</form>';
}

/**
 * Convenience method for outputting errors.
 *
 * @return void
 **/
function echo_error($error) {
  echo '<p class="error">'. $error. '</p>';
}

/**
 * Export the schema from the database specified and echo the results.
 *
 * @param string $db The key of the config to be extracted from $dbs_config.
 * @return void
 */
function export_schema($config) {
  
  $result = DbDiff::export($config['config'], $config['name']);
  
  if ($result == null) {
    echo_error('Couldn\'t connect to database: ' . mysql_error());
    return;
  }
  
  $serialized_schema = base64_encode(serialize($result));
  
  echo '<h3>Exported \'' . $config['name'] . '\'</h3>';
  
  echo '<p>Copy the following schema information and then proceed to <a href="?">step 2</a>.</p>';
  
  echo '<textarea id="textarea_snapshot_text" cols="100" rows="5" onclick="this.focus();this.select();">';
  echo chunk_split($serialized_schema, 100);
  echo '</textarea>';
  //echo " <br/>Snapshot Name:<input type='text' id='snapshot_name' value='4086811840119453_koeningen_test' /><br/>";
  //echo "          <input type='button' value='Save to repository' onclick='copy_text_to_respository()' />";
}

/**
 * Strips new line characters (CR and LF) from a string.
 *
 * @param string $str The string to process.
 * @return string The string without CRs or LFs.
 */
function strip_nl($str) {
  return str_replace(array("\n", "\r"), '', $str);
}

/**
 * Returns an 's' character if the count is not 1.
 * 
 * This is useful for adding plurals.
 *
 * @return string An 's' character or an empty string
 **/
function s($count) {
  return $count != 1 ? 's' : '';
}

/**
 * Compare the two schemas and echo the results.
 *
 * @param string $schema1 The first schema (serialized).
 * @param string $schema2 The second schema (serialized).
 * @return void
 */
function do_compare($schema1, $schema2) {
  
  if (empty($schema1) || empty($schema2)) {
    echo_error('Both schemas must be given.');
    return;
  }
  
  $unserialized_schema1 = unserialize(base64_decode(strip_nl($schema1)));
  $unserialized_schema2 = unserialize(base64_decode(strip_nl($schema2)));
  
  if(!$unserialized_schema1 or !$unserialized_schema2){
      die("<br><h2 class='info' >Schemas not serialized<h2>");
      return;  
    
  }
  
  $results = DbDiff::compare($unserialized_schema1, $unserialized_schema2,$change_1to2,$change_2to1);
  
  if (count($results) > 0) {
    
    echo '<h3>Found differences in ' . count($results) . ' table' . s(count($results)) . ':</h3>';
    
    echo '<ul id="differences">';
    foreach ($results as $table_name => $differences) {
      
      echo '<li>Table: <strong>' . $table_name . '</strong><ul>';
      foreach ($differences as $difference) {
        echo '<li>' . $difference . '</li>';
      }

      if(array_key_exists($table_name, $change_1to2)){
           echo "<li>1-2: SQL:<ul>";
        foreach ($change_1to2[$table_name] as $difference) {
            echo '<li>' . $difference . '</li>';
        } echo "</ul></li>"; 
        
        }else
            echo '<li> - No SQL Diff for '.$table_name;
        
      if(array_key_exists($table_name, $change_2to1)){
          echo "<li>2-1: SQL:<ul>";
      foreach ($change_2to1[$table_name] as $difference) {
        echo '<li>' . $difference . '</li>';        
      }
      echo "</ul></li>";
      }else
            echo '<li> - No SQL Diff for '.$table_name;
      
      
      echo '</ul></li>';
    }
    echo '</ul>';
    
  } else {
    echo '<p>No differences found.</p>';
  }
}

$host_name = $_SERVER['HTTP_HOST'];
    
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  <title>DbDiff <?php echo "on $host_name "; ?></title>
  <link rel="stylesheet" href="style.css" type="text/css" media="screen" />
</head>
<body>
    <?php
    $jquery_path = 'http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js';
    if($r=is_numeric(stripos($host_name,'localhost'))){
        //$jquery_path = 'http://localhost:8080/DbDiff/js/jquery-1.7.2.min.js';        
        $jquery_path = '/dbdiff/js/jquery-1.7.2.min.js';        
    }
    echo "\n<!-- HostName $host_name '$r'  -->";
    echo "<script src='$jquery_path' ></script>";
    ?>
    
    <script src='jquery.dump.js'  ></script>
    <script src='dbdiffrepository.js'  ></script>
    <script >

function append_to_element(element_id,msg){
    var elem = null;
    elem = document.getElementById(element_id);
    if(elem){
        if('innerHTML' in elem){
            elem.innerHTML += "<br/>"+msg;    
        }
        else{
            alert("Element " + element_id + " does not have an innerHTML!");    
            return;
        }
    }
    else{
        alert("Unable to Find element by id "+element_id);
        return;
    }
}

function set_element_to(element_id,msg){
    var elem = null;
    elem = document.getElementById(element_id);
    if(elem){
        if('innerHTML' in elem){
            elem.innerHTML = msg;    
        }
        else{
            alert("Element " + element_id + " does not have an innerHTML!");    
            return;
        }
    }
    else{
        alert("Unable to Find element by id "+element_id);
        return;
    }
}


function write_log(msg){   
    if($('#div_log').length==0){
        $('body').first().append('<div id="div_log >LOGGING CREATED EARLY DUE TO ERROR?<br/>'+msg+'</div>');
        return;
    }
    append_to_element("div_log","<div class='log_line' >"+msg+"</div>");
}

var statusBar = null;
function user_info(msg){
    if(!statusBar){
        statusBar = $('#statusBar');
        if(statusBar.length==0){
            $('body').first().before("<div class='info' style = 'background:red;font;bold'>STATUS:"+msg+"</div>");
            return;
        }
    }
    statusBar.html(msg);
    write_log('User Info : '+msg);
}


function error(msg){
    alert('ERROR:\n'+msg);
    write_log('ERROR:<br/>'+msg);
    user_info('ERROR:'+msg);
    $('#statusBar').css('background','red');
    throw msg;
}

var ctl_structure_name = "textarea_snapshot_text";


</script>
  
<div id="canvas">
<div id="statusBar" class="info" ></div>
<h1><a href="?">DbDiff <?php echo "on $host_name "; ?></a></h1>
<h2>Tool for comparing database schemas.</h2>

<?php

$action = @$_GET['a'];

switch ($action) {
  
  case 'export_schema':
  
    if (isset($_GET['db'])) {
      
      $db = $_GET['db'];
  
      if (!isset($dbs_config[$db])) {
        echo_error('No database configuration selected.');
        break;
      }

      $config = $dbs_config[$db];
      
    } else {
      
      if (!isset($_POST['db-host']) || !isset($_POST['db-user'])
        || !isset($_POST['db-password'])
        || !isset($_POST['db-name'])) {
          echo_error('No database configuration entered.');
          break;
        }
        
      $config = array(
        'name' => $_POST['db-name'] . ' (' . $_POST['db-host'] . ')',
        'config' => array(
          'host' => $_POST['db-host'],
          'user' => $_POST['db-user'],
          'password' => $_POST['db-password'],
          'name' => $_POST['db-name']
        )
      );
    }
    
    export_schema($config);
    
    echo '<p><a href="?">&laquo; Back to main page</a></p>';
    
    break;
    
  case 'compare':
    
    $schema1 = @$_POST['schema1'];
    $schema2 = @$_POST['schema2'];
    
    if (get_magic_quotes_gpc()) { // sigh...
      $schema1 = stripslashes($schema1);
      $schema2 = stripslashes($schema2);
    }
    
    do_compare($schema1, $schema2);
    
    echo '<p><a href="?">&laquo; Back to main page</a></p>';
    
    break;
    
  default:  
    show_options($dbs_config);
}

?>

  <div id='div_log' style="border:solid silver 1px; background-color;silver; height:auto; width:600px; "  >
    For logging...
  </div>  

<div id="footer">
  <p>More information on this tool is available from the corresponding <a href="http://joefreeman.co.uk/blog/2009/07/php-script-to-compare-mysql-database-schemas/">blog post</a>.</p>
  <p>Copyright &copy; 2009 <a href="mailto:joe.freeman@bitroot.com">Joe Freeman</a>. Code available under the <a href="http://en.wikipedia.org/wiki/MIT_License">MIT License</a>.</p>
</div>

</div>

</body>
</html>